package dao.impl;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;

import entity.Product_Info;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import dao.Product_InfoDao;
import util.C3P0Util;

public class Product_InfoDaoImpl implements Product_InfoDao {

	// 添加商品
	@Override
	public boolean insertProduct(Product_Info product_Info) {
		// 操作数据库对象
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		// 编写sql 语句
		String sql = "INSERT INTO `product_info` VALUES( NULL,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
		// 定义 falg 用于传递结果
		boolean flag = false;
		try {
			int i = qr.update(sql,
					new Object[] { product_Info.getProduct_code(), product_Info.getProduct_name(),
							product_Info.getBrand_id(), product_Info.getOne_category_id(),
							product_Info.getTwo_category_id(), product_Info.getThree_category_id(),
							product_Info.getPrice(), product_Info.getPublish_status(), product_Info.getAudit_status(),
							product_Info.getWeight(), product_Info.getColor_type(), product_Info.getProduction_date(),
							product_Info.getShelf_life(), product_Info.getDescript(), product_Info.getIndate(),
							product_Info.getModified_time() });
			if (i == 1) {
				flag = true;
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

	// 通过编号来查询商品
	@Override
	public Product_Info getQueryProductId(Integer product_id) {
		Product_Info product_Info = null;
		// 操作数据库对象
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		// 编写sql 语句
		String sql = "select * from `product_info` where product_id=?";
		try {
			product_Info=qr.query(sql, product_id,new BeanHandler<Product_Info>(Product_Info.class));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return product_Info;
	}

	@Override
	public List<Product_Info> getAllProduct(Integer pageNo, Integer pageSize, HashMap<String, String> map) {
		List<Product_Info> list = null;
		// 操作数据库对象
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		// 编些sql语句
		String sql = "select * from product_Info where 1=1";
		StringBuffer sb = new StringBuffer();
		sb.append(sql);
		// 根据map中的数据进行SQL拼接
		if ( map.get("searchText") != null && !"".equals(map.get("searchText"))) {
			if (map.get("searchMethod").equals("searchById") ) {
				sb.append(" and houseID = "+map.get("searchText"));
				
			} else if (map.get("searchMethod").equals("searchByName")) {
				sb.append(" and masterName = '"+map.get("searchText")+"'");	
				
			} else if (map.get("searchMethod").equals("searchAll")) {
				sb.append(" and masterName like '%"+ map.get("searchText") +"%'");
			}
		}
		sb.append(" limit ?,?");
		System.out.println(sb);
		try {
			list = qr.query(sb.toString(), new Integer[] { (pageNo - 1) * pageSize, pageSize },
					new BeanListHandler<Product_Info>(Product_Info.class));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public Integer getRows(HashMap<String, String> map) {
		// 操作数据库对象
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		// 编写sql语句
		String sql = "SELECT COUNT(1) number FROM Product_Info where 1=1";
		// 查询结果
		StringBuffer sb = new StringBuffer();
		sb.append(sql);
		// 根据map中的数据进行字符串拼接
		if ( map.get("searchText") != null && !"".equals(map.get("searchText"))) {
			if (map.get("searchMethod").equals("searchById" ) ) {
				return 1;
			} else if (map.get("searchMethod").equals("searchByName")) {
				sb.append(" and masterName = '"+map.get("searchText")+"'");	
				
			} else if (map.get("searchMethod").equals("searchAll")) {
				sb.append(" and masterName like '%"+ map.get("searchText") +"%'");
			}
		}
		System.out.println(sb);

		Long i = null;
		try {
			i = (Long) qr.query(sb.toString(), new ScalarHandler("number"));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return i.intValue();
	}

	@Override
	public List<Product_Info> getAllProduct() {
		List<Product_Info> list = null;
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());	
		String sql = "select * from product_info";
		
		try {
			list = qr.query(sql, new BeanListHandler<Product_Info>(Product_Info.class));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return list;
	}

}
